﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for DAL
/// </summary>
public class DAL
{
    const string CONNSTRING = "server=RONIL\\SQLEXPRESS;database=PracticeDB;trusted_connection=true";
    //SqlConnection conn;
    public DAL()
    {
        //conn = new SqlConnection(CONNSTRING);
    }

    public DataTable GetAllTasks()
    {
        #region Test data
        DataTable testData = new DataTable();
        testData.Columns.Add("PBI");
        testData.Columns.Add("Description");
        testData.Columns.Add("EmpID");
        testData.Columns.Add("StatusID");
        testData.Columns.Add("Priority");
        DataRow row = testData.NewRow();
        row["PBI"] = 12343;
        row["Description"] = "Photo App Requirements";
        row["EmpID"] = 9999;
        row["StatusID"] = Task.TaskStatus.InProgress;
        row["Priority"] = 1;
        testData.Rows.Add(row);
        row = testData.NewRow();
        row["PBI"] = 5643;
        row["Description"] = "Music App Requirements";
        row["EmpID"] = 11234;
        row["StatusID"] = Task.TaskStatus.ToDo;
        row["Priority"] = 1;
        testData.Rows.Add(row);
        row = testData.NewRow();
        row = testData.NewRow();
        row["PBI"] = 5655;
        row["Description"] = "Photo App Coding";
        row["EmpID"] = 9999;
        row["StatusID"] = Task.TaskStatus.ToDo;
        row["Priority"] = 1;
        testData.Rows.Add(row);
        row = testData.NewRow();
        row["PBI"] = 4785;
        row["Description"] = "Photo App Test Case Creation";
        row["EmpID"] = 11190;
        row["StatusID"] = Task.TaskStatus.InProgress;
        row["Priority"] = 1;
        testData.Rows.Add(row);
        return testData;
        #endregion Test data
        /*
        using (SqlConnection conn = new SqlConnection(CONNSTRING))
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    //conn.Open();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "TB_GetTaskData";
                    using (SqlDataAdapter adp = new SqlDataAdapter())
                    {
                        DataTable dt = new DataTable();
                        adp.SelectCommand = cmd;
                        adp.Fill(dt);
                        return dt;
                    }
                }

            }
            catch (InvalidOperationException ex)
            {
                throw ex;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        */
    }

    public bool InsertTask(int pbi, string description, int empID, Task.TaskStatus statusID, int priority)
    {
        return true;
        /*
        using (SqlConnection conn = new SqlConnection(CONNSTRING))
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    conn.Open();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "TB_InsertTask";
                    cmd.Parameters.AddWithValue("@pbi", pbi);
                    cmd.Parameters.AddWithValue("@description", description);
                    cmd.Parameters.AddWithValue("@empID", empID);
                    cmd.Parameters.AddWithValue("@statusID", Convert.ToString(statusID));
                    cmd.Parameters.AddWithValue("@priority", priority);
                    int rows = cmd.ExecuteNonQuery();
                    if (rows > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }

            }
            catch (InvalidOperationException ex)
            {
                throw ex;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        */
    }

    public bool DeleteTask(int pbi)
    {
        return true;
        /*
        using (SqlConnection conn = new SqlConnection(CONNSTRING))
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    conn.Open();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "TB_DeleteTask";
                    cmd.Parameters.AddWithValue("@pbi", pbi);
                    int rows = cmd.ExecuteNonQuery();
                    if (rows > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }

            }
            catch (InvalidOperationException ex)
            {
                throw ex;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
         */
    }

    /// <summary>
    /// To Update Task
    /// </summary>
    /// <param name="pbi">backlog id</param>
    /// <param name="empID">employee id</param>
    /// <param name="statusID">status id</param>
    /// <param name="priority">priority</param>
    /// <param name="isArchived">decides if archival is to be done. false if not to be updated</param>
    /// <returns></returns>
    public bool UpdateTask(int pbi, int empID, Task.TaskStatus statusID, int priority, bool isArchived)
    {
        return true;
        /*
        using (SqlConnection conn = new SqlConnection(CONNSTRING))
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    conn.Open();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "TB_UpdateTask";
                    cmd.Parameters.AddWithValue("@pbi", pbi);
                    cmd.Parameters.AddWithValue("@empID", empID);
                    cmd.Parameters.AddWithValue("@statusID", Convert.ToString(statusID));
                    cmd.Parameters.AddWithValue("@priority", priority);
                    cmd.Parameters.AddWithValue("@isArchived", isArchived);
                    int rows = cmd.ExecuteNonQuery();
                    if (rows > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }

            }
            catch (InvalidOperationException ex)
            {
                throw ex;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
         */
    }
}